﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;

namespace PACKETPARSERMAX20.Data
{
    public abstract class DataAccessBase
    {
        private string mConnectionString = null;

        public DataAccessBase(string pConnectionString)
        {
            mConnectionString = pConnectionString;
        }

        public DataAccessBase(string pDataBase, bool arg)
        {
            mConnectionString = GetConnectionString(pDataBase);
        }

        public MySqlConnection connection
        {
            get
            {
                return new MySqlConnection(mConnectionString);
            }
        }

        public System.Data.DataSet ExecuteGetDataSet(string pCommandText)
        {
            System.Data.DataSet dataset = new System.Data.DataSet();

            using (var con = connection)
            {
                using (MySqlDataAdapter adapter = new MySqlDataAdapter(pCommandText, con))
                {
                    adapter.UpdateBatchSize = 500;
                    adapter.ContinueUpdateOnError = false;
                    con.Open();
                    adapter.Fill(dataset);
                    con.Close();
                }
            }
            return dataset;
        }

        public System.Data.DataTable ExecuteGetDataTable(string pCommandText)
        {
            System.Data.DataTable datatable = new System.Data.DataTable();
            using (var con = connection)
            {
                using (var adapter = new MySqlDataAdapter(pCommandText, con))
                {
                    //tAdapter.UpdateBatchSize = 500;
                    adapter.ContinueUpdateOnError = false;
                    con.Open();
                    adapter.Fill(datatable);
                    con.Close();
                }
            }
            return datatable;
        }

        public int ExecuteNonQuery(string pCommandText)
        {
            int tResult = 0;

            using (var con = connection)
            {
                using (var adapter = new MySqlDataAdapter())
                {
                    adapter.UpdateBatchSize = 500;
                    adapter.ContinueUpdateOnError = false;
                    adapter.SelectCommand = new MySqlCommand(pCommandText, con);
                    con.Open();
                    tResult = adapter.SelectCommand.ExecuteNonQuery();
                    con.Close();
                }
            }
            return tResult;
        }

        public Exception TestConnection()
        {
            try
            {
                using (var con = connection)
                {
                    con.Open();
                    con.Close();
                }
            }
            catch (Exception exc)
            {
                return exc;
            }

            return null;
        }

        public List<string> GetDatabases()
        {
            return GetList("show databases;", "(not database = 'mysql' AND not database = 'information_schema')");
        }

        private List<string> GetList(string command, string select)
        {
            List<string> tList = new List<string>();

            using (var datatable = ExecuteGetDataTable(command))
            {
                var rows = datatable.Select(select, datatable.Columns[0].ColumnName + " asc");
                foreach (var row in rows)
                {
                    tList.Add(row[0].ToString());
                }
            }

            return tList;
        }

        public List<string> GetTables()
        {
            return GetList("show tables;", "");
        }

        public System.Data.DataTable GetTableColumns(string pTableName)
        {
            System.Data.DataTable tTable = ExecuteGetDataTable("DESCRIBE `" + pTableName + "`;");
            return tTable;
        }

        public static string GetConnectionString(string pDataBase)
        {
            //"DRIVER={MySQL ODBC 5.1 Driver};SERVER=" + Server + ";DATABASE=" + pDataBase + ";UID=" + UserID + ";PASSWORD=" + Password + ";OPTION=3";
            MySql.Data.MySqlClient.MySqlConnectionStringBuilder tMySqlConnectionStringBuilder = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder();
            tMySqlConnectionStringBuilder.AllowBatch = true;
            tMySqlConnectionStringBuilder.Database = pDataBase;
            tMySqlConnectionStringBuilder.UserID = "root";
            tMySqlConnectionStringBuilder.Password = "root";
            tMySqlConnectionStringBuilder.Server = "mikedb01";
            tMySqlConnectionStringBuilder.Port = 3306;
            tMySqlConnectionStringBuilder.PersistSecurityInfo = false;
            return tMySqlConnectionStringBuilder.ConnectionString;
        }
    }
}
